﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CPPaquetes.DataAccessComponents;
using CPPaquetes.BusinessEntities;
using System.IO;

namespace CPPaquetes.BusinessLayer
{
    public class Destino
    {
        CPPaquetesModel _context = new CPPaquetesModel();
        
        public List<prc_ver_destinos_web_Result> GetAllDestinations()
        {
            return  _context.prc_ver_destinos_web().ToList();

        }

        public List<TBL_DESTINO> GetAllDestinations2()
        {

            return _context.TBL_DESTINO.Where(x => x.DESTINO_PADRE_ID == null
                && x.SUPER_PADRE_ID == null
                && x.ACTIVO == "SI").OrderBy(x => x.ORDEN_WEB).ToList();
        }

        public List<prc_destino_listar_padres_Result> GetAllDestinations3()
        {
            return _context.prc_destino_listar_padres().ToList();

        }

        public List<prc_destino_listar_padres_Result> GetAllDestinations4()
        {
            string sql = "SELECT destino_id, nombre, TIPO_BAJADA_ID,ORDEN_WEB,isnull(SUPER_PADRE_ID,0) SUPER_PADRE_ID,IMAGEN_BOTONERA, IMAGEN_SUBMENU FROM TBL_DESTINO x " +
                        " WHERE x.DESTINO_PADRE_ID IS null" +
                        "  AND x.SUPER_PADRE_ID IS null" +
                        "  AND x.ACTIVO = 'SI'" +
                        "  AND EXISTS(SELECT 1 FROM TBL_PROGRAMA_DESTINO a" +
                        "                       INNER JOIN TBL_DESTINO b ON b.DESTINO_ID = a.DESTINO_ID" +
                        "                       INNER JOIN TBL_PROGRAMA c ON c.PROGRAMA_ID = a.PROGRAMA_ID" +
                        "                       WHERE b.DESTINO_PADRE_ID = x.DESTINO_ID" +
                        "                       AND b.ACTIVO = 'SI'" +
                        "                       AND c.FECHA_VIGENCIA_HASTA >= GETDATE()" +
                        "                       AND EXISTS(" +
					    "                               SELECT 1 FROM tbl_historial_programa " + 
					    "                               WHERE HISTORIAL_PROGRAMA_ID IN ( " + 
						"                                       SELECT MAX(HISTORIAL_PROGRAMA_ID) " +
                        "                                       FROM TBL_HISTORIAL_PROGRAMA  " +
                        "                                       WHERE PROGRAMA_ID = c.PROGRAMA_ID " + 
						"                                       ) " + 
					    "                                   AND ESTADO_ID = 4 " + 
                        "                               ) " + 
                        "           )" +
                        " ORDER BY x.nombre";
            //" AND c.CADUCADO = 0" +

            return _context.Database.SqlQuery<prc_destino_listar_padres_Result>(sql, new object[] { }).ToList(); 
        }



        public List<ProgramaDestino> GetProgramasDestino(int? DESTINO_PADRE_ID)
        {
            string sql = string.Format(@"SELECT TBL_DESTINO.DESTINO_ID, TBL_DESTINO.NOMBRE AS NOMBRE_DESTINO
	                                        , TBL_PROGRAMA.PROGRAMA_ID, TBL_PROGRAMA.NOMBRE AS NOMBRE_PROGRAMA
                                            , TBL_PROGRAMA.FECHA_VIGENCIA_HASTA, TBL_PROGRAMA.NRO_NOCHES
                                        FROM TBL_PROGRAMA_DESTINO 
                                         INNER JOIN TBL_DESTINO  ON TBL_DESTINO.DESTINO_ID = TBL_PROGRAMA_DESTINO.DESTINO_ID 
                                         INNER JOIN TBL_PROGRAMA  ON TBL_PROGRAMA.PROGRAMA_ID = TBL_PROGRAMA_DESTINO.PROGRAMA_ID 
                                         WHERE TBL_DESTINO.DESTINO_PADRE_ID = {0}
                                         AND TBL_DESTINO.ACTIVO = 'SI' 
                                         AND TBL_PROGRAMA.FECHA_VIGENCIA_HASTA >= GETDATE()
                                         AND EXISTS(
					                                        SELECT 1 FROM tbl_historial_programa
					                                        WHERE HISTORIAL_PROGRAMA_ID IN (
						                                        select MAX(HISTORIAL_PROGRAMA_ID) from tbl_historial_programa 
						                                        where programa_id = TBL_PROGRAMA.PROGRAMA_ID
						                                        )
					                                        AND ESTADO_ID = 4 )
                                        ORDER BY TBL_DESTINO.DESTINO_ID", DESTINO_PADRE_ID.ToString());
            //string.Format(sql, DESTINO_PADRE_ID.ToString());
            ProgramaDestino programaDestino = new ProgramaDestino();
            int DESTINO_ID = 0;

            List<ProgramaDestinoQuery> programaDestinoQuery = 
                    _context.Database.SqlQuery<ProgramaDestinoQuery>(sql, new object[] { })
                    .OrderBy(x => x.NOMBRE_DESTINO)
                    .ThenBy(y => y.NOMBRE_PROGRAMA).ToList();
            //programaDestinoQuery = programaDestinoQuery.OrderBy(x => x.NOMBRE_DESTINO).ThenBy(y => y.NOMBRE_PROGRAMA);
            //List <TBL_PROGRAMA_DESTINO> ProgramaDestino
            //    = _context.TBL_PROGRAMA_DESTINO.Include("TBL_DESTINO").Include("TBL_PROGRAMA").Where(
            //    x => x.TBL_DESTINO.DESTINO_PADRE_ID == DESTINO_PADRE_ID && x.TBL_DESTINO.ACTIVO == "SI" 
            //        && x.TBL_PROGRAMA.FECHA_VIGENCIA_HASTA >= DateTime.Today).OrderBy(x => x.TBL_DESTINO.NOMBRE).ToList();

            List<ProgramaDestino> ProgramaDestinos = new List<ProgramaDestino>();
            foreach (ProgramaDestinoQuery pd in programaDestinoQuery)
            {
                if (DESTINO_ID != pd.DESTINO_ID)
                {
                    programaDestino = new ProgramaDestino();

                    programaDestino.DESTINO_ID = pd.DESTINO_ID;
                    programaDestino.NOMBRE_DESTINO = pd.NOMBRE_DESTINO;
                    programaDestino.PROGRAMAS = new List<ProgramaDestino.Programa>();
                    ProgramaDestinos.Add(programaDestino);
                }

                
                programaDestino.PROGRAMAS.Add(new ProgramaDestino.Programa() { PROGRAMA_ID = pd.PROGRAMA_ID
                    , NOMBRE_PROGRAMA = pd.NOMBRE_PROGRAMA, NRO_NOCHES = pd.NRO_NOCHES
                    , FECHA_VIGENCIA_HASTA =   pd.FECHA_VIGENCIA_HASTA.ToString("dd MMM yyyy") });
                
                DESTINO_ID = pd.DESTINO_ID;
            }

            return ProgramaDestinos;
        }


        public List<DestinosPath> GetDestinationByPath(string PathFile, string ItemMenuName)
        {
            //bool ContainSubDirectory = false;
            List<DestinosPath> result = new List<DestinosPath>();

            if (!Directory.Exists(PathFile))
            {
                throw new Exception("La ruta '" + PathFile + "' asociada al menú, No existe");
            }            
            
            foreach (string dir in Directory.GetDirectories(PathFile))
            {
                DirectoryInfo di = new DirectoryInfo(dir);                
                DestinosPath dest = new DestinosPath();
                dest.ItemMenuName = ItemMenuName;
                dest.GroupName = di.Name;
                dest.FileNames = new List<string>();
                if (Directory.GetFiles(dir).Length > 0)
                {
                    foreach (string file in Directory.GetFiles(dir))
                    {
                        FileInfo fi = new FileInfo(file);
                        if (fi.Extension != ".db")
                        {
                            dest.FileNames.Add(fi.Name);
                        }
                    }

                    result.Add(dest);
                }

                //ContainSubDirectory = true;
            }

            if (Directory.GetFiles(PathFile).Length > 0)
            {
                DirectoryInfo di = new DirectoryInfo(PathFile);
                DestinosPath dest = new DestinosPath();
                dest.ItemMenuName = ItemMenuName;
                dest.GroupName = "";// di.Name;
                dest.FileNames = new List<string>();

                foreach (string file in Directory.GetFiles(PathFile))
                {
                    FileInfo fi = new FileInfo(file);
                    if (fi.Extension != ".db")
                    {
                        dest.FileNames.Add(fi.Name);
                    }
                }

                result.Add(dest);

            }
            return result;
        }
    }
}

